package org.bigdata.framework.admin.web.util;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.read.biff.BiffException;
import jxl.write.Border;
import jxl.write.BorderLineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class DBUtils {

	/**
	 * @param args
	 * @throws IOException 
	 * @throws BiffException 
	 */
	
	public static void writeExcel(String path,List<HashMap<String, Object>> list,int flag){
		try {   
			// 创建Excel工作薄   
			// System.out.println("123123");
			WritableWorkbook wwb;   
			// 新建立一个jxl文件,即在d盘下生成testJXL.xls   
			OutputStream os = new FileOutputStream(path);   
			wwb=Workbook.createWorkbook(os);    
			// 添加第一个工作表并设置第一个Sheet的名字   
			WritableSheet sheet = wwb.createSheet("0", 0);   
			int i=1; 
       	  	for (Map<String, Object> m : list){  
       	  		int j=0;
       	  		Label label = null; 
       	  		for (String k : m.keySet()){  
       	  			if(i==1){
       	  				label = new Label(j,0,k.toString(),getHeader()); 
       	  				sheet.addCell(label);  
       	  			}
       	  			if(flag==1){
       	  				label = new Label(j,i,m.get(k).toString()); 
       	  				sheet.addCell(label); 
       	  			}
       	  			j++;
       	  		}  
       	  		i++;
       	  	}
       	  	// 写入数据   
            wwb.write();   
            // 关闭文件   
            wwb.close();   
         } catch (Exception e) {    
            e.printStackTrace();   
        }   
	 }
	/**
	 * @param args
	 * @throws IOException 
	 * @throws BiffException 
	 */
	
	public static void writeDynamicExcel(String path,String classenZh,List<HashMap<String, Object>> list,int flag){
		try {   
			// 创建Excel工作薄   
			// System.out.println("123123");
			WritableWorkbook wwb;   
			// 新建立一个jxl文件,即在d盘下生成testJXL.xls   
			OutputStream os = new FileOutputStream(path);   
			wwb=Workbook.createWorkbook(os);    
			// 添加第一个工作表并设置第一个Sheet的名字   
			WritableSheet sheet = wwb.createSheet("0", 0);   
			int i=1; 
       	  	for (Map<String, Object> m : list){  
       	  		int j=0;
       	  		Label label = null; 
       	  		for (String k : m.keySet()){  
       	  			if(i==1){
       	  				label = new Label(j,0,k.toString(),getHeader()); 
       	  				sheet.addCell(label);  
       	  			}
       	  			if(flag==1){
       	  				label = new Label(j,i,m.get(k).toString()); 
       	  				sheet.addCell(label); 
       	  			}
       	  			j++;
       	  		}  
       	  		i++;
       	  	}
       	  	// 写入数据   
            wwb.write();   
            // 关闭文件   
            wwb.close();   
         } catch (Exception e) {    
            e.printStackTrace();   
        }   
	 }
	/**
	 * @param args
	 * @throws IOException 
	 * @throws BiffException 
	 */
	
	public static void writeExcelList(String path,HashMap<String, List<HashMap<String, Object>>> excelMap,int flag){
		try {   
			// 创建Excel工作薄   
			// System.out.println("123123");
			WritableWorkbook wwb;   
			// 新建立一个jxl文件,即在d盘下生成testJXL.xls   
			OutputStream os = new FileOutputStream(path);   
			wwb=Workbook.createWorkbook(os);
			Integer sheetType = 0;
			for(String key:excelMap.keySet()){
				// 添加第一个工作表并设置第一个Sheet的名字   
				WritableSheet sheet = wwb.createSheet(key,sheetType);   
				int i=1; 
				for (Map<String, Object> m : excelMap.get(key)){  
					int j=0;
					Label label = null; 
					for (String k : m.keySet()){  
						if(i==1){
							label = new Label(j,0,k.toString(),getHeader()); 
							sheet.addCell(label);  
						}
						if(flag==1){
							label = new Label(j,i,m.get(k).toString()); 
							sheet.addCell(label); 
						}
						j++;
					}  
					i++;
				}
				sheetType ++;
			}
			
			// 写入数据   
			wwb.write();   
			// 关闭文件   
			wwb.close();   
		} catch (Exception e) {    
			e.printStackTrace();   
		}   
	}
	public static List<HashMap<String, Object>> readExcel(String path) throws BiffException, IOException{
		   //创建一个list 用来存储读取的内容
		    List list2 = new ArrayList();
		    Workbook rwb = null;
		    Cell cell = null;
		    InputStream stream = new FileInputStream(path);
		    //获取Excel文件对象
		    rwb = Workbook.getWorkbook(stream);    
		    //获取文件的指定工作表 默认的第一个
		    Sheet sheet = rwb.getSheet(0); 
			List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
		    //行数(表头的目录不需要，从1开始)
		    for(int i=1; i<sheet.getRows(); i++){	
			    LinkedHashMap<String, Object> map = new LinkedHashMap<String, Object>();
		     //列数
		     for(int j=0; j<sheet.getColumns(); j++){
		      //获取第i行，第j列的值
		      map.put(sheet.getCell(j,0).getContents(), sheet.getCell(j,i).getContents()); 
		      
		     }
		     list.add(map);
		    }
        return list;
	}
	public static HashMap<String, List<HashMap<String, Object>>> readExcelAll(String path) throws BiffException, IOException{
		//创建一个list 用来存储读取的内容
		HashMap<String, List<HashMap<String, Object>>> excelMap = new HashMap<String, List<HashMap<String, Object>>>();
	    List list2 = new ArrayList();
	    Workbook rwb = null;
	    Cell cell = null;
	    InputStream stream = new FileInputStream(path);
	    //获取Excel文件对象
	    rwb = Workbook.getWorkbook(stream);   
	    List<HashMap<String, Object>> list = null;
	    for (int k = 0; k < rwb.getNumberOfSheets(); k++) {
	    	//获取文件的指定工作表 默认的第一个
		    Sheet sheet = rwb.getSheet(k); 
			list = new ArrayList<HashMap<String, Object>>();
		    //行数(表头的目录不需要，从1开始)
		    for(int i=1; i<sheet.getRows(); i++){	
			    LinkedHashMap<String, Object> map = new LinkedHashMap<String, Object>();
		     //列数
		     for(int j=0; j<sheet.getColumns(); j++){
		      //获取第i行，第j列的值
		      map.put(sheet.getCell(j,0).getContents(), sheet.getCell(j,i).getContents()); 
		      
		     }
		     list.add(map);
		    }
		    
		    excelMap.put(sheet.getName(), list);
	    }
	    return excelMap;
	}
	public static WritableCellFormat getHeader(){
		   WritableFont font = new  WritableFont(WritableFont.TIMES, 10 ,WritableFont.BOLD);//定义字体
		   try {
		    font.setColour(Colour.BLACK);//蓝色字体
		   } catch (WriteException e1) {
		    // TODO 自动生成 catch 块
		    e1.printStackTrace();
		   }
		   WritableCellFormat format = new  WritableCellFormat(font);
		   try {
		    format.setAlignment(jxl.format.Alignment.CENTRE);//左右居中
		    format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//上下居中
		    format.setBorder(Border.ALL,BorderLineStyle.THIN,Colour.BLACK);//黑色边框
		    format.setBackground(Colour.BLACK);//黄色背景
		   } catch (WriteException e) {
		    // TODO 自动生成 catch 块
		    e.printStackTrace();
		   }
		   return format;
		  }
	public static void main(String[] args) throws BiffException, IOException {
		List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
		//list=readExcel("d:\\testJXL.xls");
		  for (Map<String, Object> m : list)  
		    {  
		      for (String k : m.keySet())  
		      {  
		        System.out.println(k + " : " + m.get(k));  
		      }  
		  
		  }
		  LinkedHashMap<String, Object>	map = new LinkedHashMap<String, Object>();
			
		    map.put("a", "a1");  
		    map.put("b", "b1");
		    map.put("c", "c1");
			List<HashMap<String, Object>> list2 = new ArrayList<HashMap<String, Object>>();  
		    list2.add(map);
		    LinkedHashMap<String, Object> map2 = new LinkedHashMap<String, Object>();
		    map2.put("a", "a2");  
		    map2.put("b", "b2");
		    map2.put("c", "c2");
		    LinkedHashMap<String, Object> map3 = new LinkedHashMap<String, Object>();
		    list2.add(map2);
		    map3.put("a", "a3");  
		    map3.put("b", "b3"); 
		    map3.put("c", "c3");
		    list2.add(map3);
		  writeExcel("d:\\testJXL.xls",list2,0);
	}
	
	
}
